#delimit;
clear;

*>>> TANF;

save 20161011_TANF_1014.dta, replace emptyok;

forvalues i=2010/2014{; 

import excel using 20161011_WRD_TANF_Clean.xlsx, sheet(`i') cellra(A1:B52) firstrow clear;
gen year=`i';

append using 20161011_TANF_1014.dta;
sort State;

save 20161011_TANF_1014.dta, replace;

};

collapse MAX_TANF, by(State);
merge 1:1 State using "FIPS.dta", nogen;
drop if FIPS==.;
save 20161011_TANF_1014.dta, replace;



*>>> UI;
insheet using 20161011_awb_st_cy.csv, clear names;

rename st state_alpha;

gen year=substr(rptdate,8,4);
destring year, replace;
keep if year>=2010 & year<=2014;

rename average AWBA;
destring AWBA, replace;

collapse AWBA, by(state);
merge 1:1 state_alpha using "FIPS.dta", nogen;
drop if FIPS==.;
save 20161012_AWBA_1014.dta, replace;


use "20161021_State_Parameters_w_Var.dta", clear;
merge 1:1 FIPS using 20161012_AWBA_1014.dta, nogen;
merge 1:1 FIPS using 20161011_TANF_1014.dta, nogen;
save 20161021_State_Parameters_w_Var_Policy.dta, replace;

drop if FIPS==2 | FIPS==15;

replace R_2=R_2*100;
replace AWBA=AWBA*4;

regress R_2 AWBA;
regress R_2 MAX_TANF;


twoway scatter R_2 AWBA, ml(state) mlabc("gs10") m(i) mlabp(0)
  xline(1165.356, lc("gs10")) yline(41.57796 , lc("gs10")) 
 xlabel(750(100)1650, labsize(medium) tlc(gs14) format(%3.0f))
 ylabel(30(5)60, labsize(medium) glcolor("gs14") tlc(gs14)) 
 xscale(lc(gs14)) yscale(lc(gs14))
 legend(off) graphregion(color(white))   
 xtitle("Average Monthly UI Benefit Amount")   ytitle("Poverty Relief (R)")
 || lfit R_2 AWBA, lc(gs2) ;

graph save "20161021_R_UI.gph", replace; 



twoway scatter R_2 MAX, ml(state) mlabc("gs10") m(i) mlabp(0)
  xline(425, lc("gs10")) yline(41.57796 , lc("gs10")) 
 xlabel(150(50)750, labsize(medium) tlc(gs14) format(%3.0f))
 ylabel(30(5)60, labsize(medium) glcolor("gs14") tlc(gs14)) 
 xscale(lc(gs14)) yscale(lc(gs14))
 legend(off) graphregion(color(white))   
 xtitle("Maximum Monthly TANF Benefit")   ytitle("Poverty Relief (R)")
 || lfit R_2 MAX, lc(gs2) ;

graph save "20161021_R_TANF.gph", replace; 

set scheme s1mono;
graph combine "20161021_R_UI.gph" "20161021_R_TANF.gph", rows(1) ;


graph display, xsize(12) ysize(6);

graph export "20161021_R_UI_TANF.pdf", fontface(Helvetica) replace;
